package Database;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.HashMap;
import java.sql.PreparedStatement;
import MessagePacks.*;
import entities.NotificationMessage;
import entities.login;

/**
 *@author Juan Alapin - 324507516
 *@author Giora Yaffe - 036551281
 *@author Shachar Gritzman - 300174026
 *@author Nir Porat - 021515580
*/

public class mysqlConnection {

	private Connection conn;
	private static final String DEFAULT_database = "jdbc:mysql://localhost/ICM";
	private static final String driver = "com.mysql.jdbc.Driver";
	private static final String DEFAULT_username = "root";
	private static final String DEFAULT_password = "braude";

	private String database;
	private String username;
	private String password;
	private Statement stmt;
	
	/**
	 * Constructor
	 * @param database:String
	 * @param username:String
	 * @param password:String
	 */
	public mysqlConnection (String database, String username, String password) 
	{
		try 
		{
            Class.forName(driver).newInstance();
        } catch (Exception ex) {/* handle the error*/}
        
        try 
        {
        	this.database = new String(database);
			this.username = new String(username);
			this.password = new String(password);
			conn = DriverManager.getConnection(this.database,this.username,this.password);
            System.out.println("SQL connection succeed");

     	} catch (SQLException ex) 
     	    {/* handle any errors*/
            System.out.println("SQLException: " + ex.getMessage());
            System.out.println("SQLState: " + ex.getSQLState());
            System.out.println("VendorError: " + ex.getErrorCode());
            }
   	}
	
	/**
	 * Constructor
	 * @throws Exception
	 */
	public mysqlConnection() throws Exception {
		try {
			Class.forName(driver).newInstance();
		} catch (Exception ex) {
			throw ex;
		}

		try {
			conn = DriverManager.getConnection(DEFAULT_database,
					DEFAULT_username, DEFAULT_password);
		} catch (SQLException ex) {
			System.out.println("SQLException: " + ex.getMessage());
			System.out.println("SQLState: " + ex.getSQLState());
			System.out.println("VendorError: " + ex.getErrorCode());
			throw ex;
		}
	}
	
	/**
	 * function will be activated in the end of each phase.
	 * then the function will check if there was a time exception,
	 * if yes, it will document it in database
	 * @param proID - the processID that is check
	 */
	public void updateExceptions(int proID)
	{
		ResultSet qrs = null;
		int timeException=0;
		int level=0;
		String getProcDet = new String("SELECT * FROM `ICM`.`processtime` P WHERE P.processID = '"+proID+"';");
		try {
			stmt = conn.createStatement();
			qrs = stmt.executeQuery(getProcDet);
			if(qrs.next())
						{
						
						if(Integer.parseInt(qrs.getString(2))<0)
							{
							timeException=Integer.parseInt(qrs.getString(2));
							level=Integer.parseInt(qrs.getString(3));
							String writeException = new String("INSERT INTO `ICM`.`timeexceptions`(ProcessID,phase,time) VALUES('" + proID + "','" + level +"','"+ timeException +"');");
							stmt = conn.createStatement();
							stmt.executeUpdate(writeException);
							}
						 }
			qrs.close();
		} catch (SQLException e) {

			e.printStackTrace();
		}
	
	}

	
	/**
	 * function gets all the roles of the selected usert in the selected process
	 * @param ProcID
	 * @param usrID
	 * @return
	 * @throws SQLException
	 */
	public ArrayList<String> getAllRolesProc(int ProcID, int usrID) throws SQLException{
		ArrayList<String> mMap = new ArrayList<String>();
		ResultSet qrs = null;
		String query = new String("SELECT * FROM `ICM`.`process` P WHERE P.ProcessID = '"+ProcID+"';");
		stmt = conn.createStatement();
		qrs = stmt.executeQuery(query);
		if (qrs.next())
			{
			if(qrs.getInt(3)==usrID)
				mMap.add("Supervisor");
			if(qrs.getInt(4)==usrID)
				mMap.add("Estimator");
			if(qrs.getInt(5)==usrID)
				mMap.add("chairman");
			if(qrs.getInt(6)==usrID)
				mMap.add("Execution Leader");
			if(qrs.getInt(7)==usrID)
				mMap.add("Tester");
			}
		qrs.close();
		return(mMap);
	}
	
	
	
	
	/**
	 * Update the date for unsuspend
	 * @param procID:int - The process ID
	 */
	public void updUnsusDate(int procID){
		String query = new String("UPDATE `ICM`.`processDates` L SET `UnSuspendDate` = CURDATE() WHERE L.ProcID = "+ procID + ";");
		try {
			stmt = conn.createStatement();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		try {
			stmt.executeUpdate(query);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
	}
	
	
	
	
	/**
	 * Determine tester
	 * @param proID:int - the process ID
	 * @param testerID:int - Tester ID
	 */
	public void setTester(int proID, int testerID){
		String query = new String("UPDATE `ICM`.`process` L SET `TesterID` = "+testerID+" WHERE L.ProcessID = "+ proID + ";");
		try {
			stmt = conn.createStatement();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		try {
			stmt.executeUpdate(query);
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	/**
	 * Get the process time
	 * @param procID:int - The process ID
	 * @return 
	 * @throws SQLException
	 */
	public int getTimeProc(int procID) throws SQLException{
		int ret = login.ERROR;
		ResultSet qrs = null;
		String query = new String("SELECT * FROM `ICM`.`processtime` P WHERE P.processID = '"+procID+"';");
		stmt = conn.createStatement();
		qrs = stmt.executeQuery(query);
		if(qrs.next()){
			ret= Integer.parseInt(qrs.getString(2));
		}
		
		return ret;
		
	}
	
	/**
	 * Get all the reports
	 * @return ArrayList<String>
	 * @throws SQLException
	 */
	public ArrayList<String> getAllReportsMem1() throws SQLException{
		ArrayList<String> mMap = new ArrayList<String>();
		ResultSet qrs = null;
		String query = new String("SELECT * FROM `ICM`.`EstReports` P WHERE P.mem1ReadOk = '0';");
		stmt = conn.createStatement();
		qrs = stmt.executeQuery(query);
		while (qrs.next())
		{
			mMap.add(qrs.getString(1));
		}
		qrs.close();
		return(mMap);
		
		
	}
	
	/**
	 * Get all the reports
	 * @return ArrayList<String>
	 * @throws SQLException
	 */
	public ArrayList<String> getAllReportsMem2() throws SQLException{
		ArrayList<String> mMap = new ArrayList<String>();
		ResultSet qrs = null;
		String query = new String("SELECT * FROM `ICM`.`EstReports` P WHERE P.mem2ReadOk = '0';");
		stmt = conn.createStatement();
		qrs = stmt.executeQuery(query);
		while (qrs.next())
		{
			mMap.add(qrs.getString(1));
		}
		qrs.close();
		return(mMap);
		
		
	}
	
	/**
	 * function gets the committee members from the DB and returns their Ids And Roles In The Committee
	 * @return HashMap<String,String> - <Id,Role>
	 * @throws SQLException - When No Committee Member In Table
	 */
	public HashMap<String,String> getCommitteeMembers() throws SQLException{
		HashMap<String, String> mMap = new HashMap<String, String>();
		ResultSet qrs = null;
		String query = new String("SELECT * FROM `ICM`.`committeemembers`;");
		stmt = conn.createStatement();
		qrs = stmt.executeQuery(query);
		while (qrs.next())
			{
			mMap.put(qrs.getString(1),qrs.getString(2));
			}
		return(mMap);
	}
	
	/**
	 * function gets all the processes of that the user initiated from DB
	 * @param initId:int - the user ID
	 * @return ArrayList<String> - <processID> - List Of ProcessIds
	 * @throws SQLException - When there are no processes of this initiator
	 */
	public ArrayList<String> getAllProcessForUser(int initId) throws SQLException{
		ArrayList<String> mMap = new ArrayList<String>();
		ResultSet qrs = null;
		String query = new String("SELECT * FROM `ICM`.`process` P WHERE P.InitID = "+initId+";");
		stmt = conn.createStatement();
		qrs = stmt.executeQuery(query);
		while (qrs.next()) {
			mMap.add(qrs.getString(1));
		}
		qrs.close();
		return mMap;
		
	}
	
	
	
	
	/**
	 * Retrieve data from process
	 * @throws SQLException - When The Wanted ProcessID does not exist in process table in DB 
	 */
	public void getAllDataFromProc(OneProcessPack prcPck) throws SQLException{
		
		ResultSet qrs = null;
		ResultSet qrs2 = null;
		String query = new String("SELECT * FROM `ICM`.`process` P WHERE P.ProcessID = "+prcPck.getProcessID()+";");
		stmt = conn.createStatement();
		qrs = stmt.executeQuery(query);
		if(qrs.next()){
			prcPck.setInitiatorID(qrs.getInt(2));
			prcPck.setSupervisorID(qrs.getInt(3));
			prcPck.setEstimatorID(qrs.getInt(4));
			prcPck.setChairmanID(qrs.getInt(5));
			prcPck.setExecLeaderID(6);
			prcPck.setTesterID(7);
			prcPck.setStatus(qrs.getString(8));
			prcPck.setCurrentPhase(qrs.getInt(9));
			prcPck.setResponsableOK(qrs.getInt(10));
		}
		qrs.close();
		String query2 = new String("SELECT * FROM `ICM`.`processdates` P WHERE P.ProcID = "+prcPck.getProcessID()+";");
					stmt = conn.createStatement();
					qrs2 = stmt.executeQuery(query2);
				if(qrs2.next()){
						prcPck.setStartDate(qrs2.getString(2));
						if(prcPck.getStatus().equals("Ended")||prcPck.getStatus().equals("Terminated")){
							prcPck.setEndDate(qrs2.getString(3));}
						else{
							prcPck.setEndDate("no date.");
						}
			
					}
		qrs2.close();
				
	}
	
	
	/**
	 * 
	 * @param userID:int
	 * @param password:String
	 * @return 
	 * @throws SQLException
	 */
	public int getUser(int userID, String password) throws SQLException
	{
		int answer = login.ERROR;
		
		ResultSet qrs = null;
		String query = new String("SELECT * FROM `icm`.`login` L WHERE L.userID = " + userID + " AND L.Password = '" + password + "' ;");
		stmt = conn.createStatement();
		qrs = stmt.executeQuery(query);
		if (qrs.next()) {
			if (qrs.getString(4).equals("0")) {
				query = new String("UPDATE `ICM`.`Login` L SET `LoggedIn` = 1 WHERE L.userID = "+ userID + ";");
				stmt = conn.createStatement();
				stmt.executeUpdate(query);
				answer = Integer.parseInt(qrs.getString(3));

			} else
				answer = login.ALREADY_LOGGED_IN;
		}
		qrs.close();
		
		return answer;
	}
	
	/**
	 * 
	 * @param userID:int - The user ID
	 */
	public void LogoutUser(int userID) {
		String query = new String("UPDATE `ICM`.`Login` L SET `LoggedIn` = 0 WHERE L.userID = "+ userID + ";");
		
		try {
			stmt = conn.createStatement();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		try {
			stmt.executeUpdate(query);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	/**
	 * 
	 * @param userID:int - The user ID
	 * @return HashMap<String, String>
	 * @throws SQLException
	 */
	public HashMap<String, String> GetProcess(int userID) throws SQLException{

		HashMap<String, String> mMap = new HashMap<String, String>();
		ResultSet qrs = null;
		String query = new String("SELECT * FROM `ICM`.`process` P WHERE P.EstimatorID = " + userID + " OR P.supervisorID= " + userID + " OR P.ChairmanID= " + userID + " OR P.ExecLeaderID= " + userID + " OR P.TesterID= " + userID + ";");
		stmt = conn.createStatement();
		qrs = stmt.executeQuery(query);
		while (qrs.next()) {
			if(!qrs.getString(8).equals("freeze")&&!qrs.getString(8).equals("Ended")){
				if(userID==qrs.getInt(3))
					mMap.put(qrs.getString(1), "Supervisor"); 
			
				if(userID==qrs.getInt(4))
					mMap.put(qrs.getString(1), "Estimator");
			
				if(userID==qrs.getInt(5))
					mMap.put(qrs.getString(1), "chairman");
			
				if(userID==qrs.getInt(6))
					mMap.put(qrs.getString(1), "Execution Leader");
			
				if(userID==qrs.getInt(7))
					mMap.put(qrs.getString(1), "Tester");
			}

		}
		qrs.close();
		return mMap;

	}
	
	/**
	 * function gets all the processes that don't have a Supervisor yet
	 * @return ArrayList<String> - <processID> - List Of ProcessIds
	 * @throws SQLException - When there are no such processes
	 */
	public ArrayList<String> GetAllNewProcess() throws SQLException{

		ArrayList<String> mMap = new ArrayList<String>();
		ResultSet qrs = null;
		String query = new String("SELECT * FROM `ICM`.`process` P WHERE P.Status = 'new';");
		stmt = conn.createStatement();
		qrs = stmt.executeQuery(query);
		while (qrs.next()) {
			mMap.add(qrs.getString(1));
		}
		qrs.close();
		return mMap;
	}
	
	/**
	 * function gets all the Information Systems Department workers
	 * @return ArrayList<String> - <userID> - List Of user Ids
	 * @throws SQLException - When there are no workers
	 */
	public ArrayList<String> GetAllWorkers() throws SQLException{

		ArrayList<String> mMap = new ArrayList<String>();
		ResultSet qrs = null;
		String query = new String("SELECT * FROM `ICM`.`login` L  WHERE L.permisionLevel = 4 ;");
		stmt = conn.createStatement();
		qrs = stmt.executeQuery(query);
		while (qrs.next()) {
			mMap.add(qrs.getString(1));
		}
		qrs.close();
		return mMap;
	}
	
	/**
	 * function gets all the processes that have been unFreezed by the selected Supervisor
	 * @param supID:int - supervisor iD
	 * @return ArrayList<String> - <processID> - List of process ids
	 * @throws SQLException - When there are not any unfreezed processes
	 */
	public ArrayList<String> GetAllUnFreezeProcess(int supID) throws SQLException{

		ArrayList<String> mMap = new ArrayList<String>();
		ResultSet qrs = null;
		String superId = Integer.toString(supID);
		String query = new String("SELECT * FROM `ICM`.`process` P WHERE P.Status != 'Suspended' AND P.Status != 'Ended' AND P.Status != 'Terminated';");
		stmt = conn.createStatement();
		qrs = stmt.executeQuery(query);
		while (qrs.next()) {
			String supFrQry= qrs.getString(3);
			if(superId.equals(supFrQry))
				mMap.add(qrs.getString(1));
		}
		qrs.close();
		return mMap;
	}
		
	/**
	 * function gets all the processes that have been Freezed
	 * @return ArrayList<String> - <processID> - List of process ids
	 * @throws SQLException - When there are no any frozen processes
	 */
	public ArrayList<String> GetAllFreezeProcess() throws SQLException{

		ArrayList<String> mMap = new ArrayList<String>();
		ResultSet qrs = null;
		String query = new String("SELECT * FROM `ICM`.`process` P WHERE P.Status = 'Suspended';");
		stmt = conn.createStatement();
		qrs = stmt.executeQuery(query);
		while (qrs.next()) {
			mMap.add(qrs.getString(1));
		}
		qrs.close();
		return mMap;
	}
	
	/**
	 * Method returns all ths ids that are different than currentID in The PerLvl
	 * Example: getAllMenDifferentFromIdInDB(111,4) returns all the workers that are have id different than 111
	 * @return HashMap<String,String>
	 * @throws SQLException 
	 */
	public HashMap<String,String> getAllMenDifferentFromIdInDB(int currentID,int perLvl) throws SQLException{
	HashMap<String, String> mMap = new HashMap<String, String>();
	ResultSet diffusers=null;
	String query = new String("SELECT userID,userName,userLastname FROM `ICM`.`login` L WHERE L.userID<>" + currentID + " AND L.permisionlevel=" + perLvl + ";");
	stmt = conn.createStatement();
	diffusers = stmt.executeQuery(query);
	while(diffusers.next()){
		System.out.println(diffusers.getString(1) +" - "+ diffusers.getString(2) + " " + diffusers.getString(3));
		mMap.put(diffusers.getString(1),diffusers.getString(2) + " " + diffusers.getString(3));
	}
	diffusers.close();	
	return(mMap);	
	}

	/**
	 * Adds New Request To Database
	 * @param initID:int - the request initiator ID
	 * @param sys:String - the name of the selected system for change
	 * @param curDesc:String - description of the current state 
	 * @param changeDesc:String - description of the change
	 * @param reas:String - the reason for change
	 * @param comment:String - extra comments
	 * @return Request Id:int (Generated In DB)
	 * @throws SQLException - when insert to DB failed
	 */
	public int addRequestToDB(int initID,String sys,String curDesc,String changeDesc,String reas,String comment) throws SQLException{
		int ret=0;
		String addQuery = new String("INSERT INTO `ICM`.`requests`(InitID,System,CurDesc,ChangeDesc,Reason,Comment) VALUES (" + initID + ",'" + sys + "','" + curDesc + "','" + changeDesc + "','" + reas + "','" + comment+"');");
		String getQuery = new String("SELECT * FROM `ICM`.`requests` R WHERE R.InitID='" + initID + "'AND R.System='" + sys +"' AND R.CurDesc='" + curDesc + "' AND R.Reason='" + reas + "' AND R.Comment='" + comment +"';");
		ResultSet qrs = null;
		stmt = conn.createStatement();
		stmt.executeUpdate(addQuery);
		qrs = stmt.executeQuery(getQuery);
		if(qrs.next())
			ret = qrs.getInt(1);
		qrs.close();
		System.out.println(ret);
		return(ret);
	}
	
	/**
	 * Update the file name in request
	 * @param reqID - Request ID
	 * @param fileName - File name
	 */
	public void setFileNameInRequest(int reqID,String fileName){
		String query = new String("UPDATE `ICM`.`requests` R SET R.FileName = '" +fileName+ "' WHERE R.ReqID = '"+reqID+"';");
		try {
			stmt = conn.createStatement();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		try {
			stmt.executeUpdate(query);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	
	
	/**
	 * function adds a new process To DB - after getting the processID from the requestID
	 * @param reqID:int - the new processId (Generated in DB by first adding the request)
	 * @param initID:int - The process initiator
	 * @throws SQLException - when adding the process to DB failed
	 */
	public void addProcessToDB(int reqID,int initID) throws SQLException{
		ResultSet qrs = null;
		String query = new String ("SELECT cmID FROM `ICM`.`committeemembers` P WHERE P.cmRole = 'Chairman';");
		stmt = conn.createStatement();
		qrs = stmt.executeQuery(query);
		int chairmanId = 0;
		if(qrs.next()){
			 chairmanId = qrs.getInt(1);}
		
		String addQuery = new String("INSERT INTO `ICM`.`process`(ProcessID,InitID,ChairmanID,Status) VALUES('" + reqID + "','" + initID +"','"+ chairmanId +"','new');");
		stmt = conn.createStatement();
		stmt.executeUpdate(addQuery);
		
		String dateQuery = new String("INSERT INTO `ICM`.`processDates`(ProcID,startDate,status) VALUES('" + reqID + "', CURDATE() ,'new');");
		stmt = conn.createStatement();
		stmt.executeUpdate(dateQuery);
	}
	
	/**
	 * function updates a process status
	 * @param procId:int - ID of the process that needs to be updated
	 * @param status:String - the new status of the process
	 */
	public void updateProcessStatus(int procId, String status) {
		
		String dateQuery = new String("UPDATE `ICM`.`processDates` P SET P.Status = '" +status+ "' WHERE P.ProcID = '"+procId+"';");
		String query = new String("UPDATE `ICM`.`process` P SET P.Status = '" +status+ "' WHERE P.ProcessID = '"+procId+"';");
		String endQuery = new String("UPDATE `ICM`.`processDates` P SET P.Status = '" +status+ "' , P.endDate = CURDATE() WHERE P.ProcID = '"+procId+"';");
		String suspendQuery = new String("UPDATE `ICM`.`processDates` P SET P.Status = '" +status+ "' , P.suspendDate = CURDATE() WHERE P.ProcID = '"+procId+"';");
		try {
			stmt = conn.createStatement();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		try {
			stmt.executeUpdate(query);
			
			if(status.equals("Active"))
				stmt.executeUpdate(dateQuery);
			else if(status.equals("Ended")||status.equals("Terminated")){
				stmt.executeUpdate(endQuery);
			}
			else if(status.equals("Suspended")){
				stmt.executeUpdate(suspendQuery);
			}
				
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	/**
	 * function updates a process phase
	 * @param procId:int - ID of the process that needs to be updated
	 * @param phaseNm:int - the new phase of the process
	 */
	public void updateProcessPhase (int procId , int phaseNm){
		String query = new String("UPDATE `ICM`.`process` P SET P.CurPhase = '"+phaseNm+"' WHERE P.ProcessID = '"+procId+"';");
		try {
			stmt = conn.createStatement();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		try {
			stmt.executeUpdate(query);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
	}
	
	/**
	 * function updates a process Resposible - sets the flag that says if the current phase got a phase manager
	 * @param procId:int - ID of the process that needs to be updated
	 * @param ok:int - 1-have a phase manager or 0-dont have a phase manager
	 */
	public void updateProcessResponsible(int procId, int Ok){
		String query = new String("UPDATE `ICM`.`process` P SET P.ResponsibleOK = '"+Ok+"' WHERE P.ProcessID = '"+procId+"';");
		try {
			stmt = conn.createStatement();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		try {
			stmt.executeUpdate(query);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	/**
	 * function changes the process current supervisor to another supervisor
	 * @param procId:int - the process id that we want to change it's supervisor
	 * @param SupervisorID:int - the new supervisor id
	 */
public void ChangeSupervisor (int procId , int SupervisorID){
		
		String query = new String("UPDATE `ICM`.`process` P SET P.supervisorID = '" +SupervisorID+ "' WHERE P.ProcessID = '"+procId+"';");
		try {
			stmt = conn.createStatement();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		try {
			stmt.executeUpdate(query);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
   }

	/**
	 * function changes the process current Estimator to another Estimator
	 * @param procId:int - the process id that we want to change it's Estimator
	 * @param EstimatorID:int - the new Estimator id
	 */
	public void ChangeEstimator (int procId , int EstimatorID){
		
		String query = new String("UPDATE `ICM`.`process` P SET P.EstimatorID = '" +EstimatorID+ "' WHERE P.ProcessID = '"+procId+"';");
		try {
			stmt = conn.createStatement();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		try {
			stmt.executeUpdate(query);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
	}

	/**
	 * function gets all the processes that are waiting for approval
	 * @return HashMap<String, String> - <processID,TimeFromBegin>
	 * @throws SQLException - when there are no processes waiting for approval
	 */
	public HashMap<String, String> GetProcessWaitingConf() throws SQLException{
		HashMap<String, String> mMap = new HashMap<String, String>();
		ResultSet qrs = null;
		String query = new String("SELECT * FROM `ICM`.`process` P WHERE P.Status = 'waiting for approval';");
		stmt = conn.createStatement();
		qrs = stmt.executeQuery(query);
		while (qrs.next()) {
			mMap.put(qrs.getString(1), qrs.getString(4));
		}
		qrs.close();
		return mMap;
	}
	
	/**
	 * function create time for the selected level of the selected process
	 * @param procId:int - The process ID
	 * @param time:int - Level time
	 * @throws SQLException
	 */
	public void CreateTimeForLevel(int procId, int time) throws SQLException{
		String query = new String("INSERT INTO `ICM`.`processtime` (processID,timeEntered,Level,timeSinceBegin) VALUES ('"+procId+"' ,'"+time+"','1','0');");
		stmt = conn.createStatement();
		stmt.executeUpdate(query);
	}
	
	/**
	 * function updates the time for the selected level of the selected process
	 * @param procId:int - ID of the selected process
	 * @param time:int - the new time for the phase of the selected process
	 * @param level:int - the phase of the selected process
	 * @throws SQLException
	 */
	public void UpdateTimeForLevel(int procId, int time, int level) throws SQLException{
		String query = new String("UPDATE `ICM`.`processtime` P SET P.timeEntered = '"+time+"' WHERE P.processID = '"+procId+ "';");
		String query1 = new String("UPDATE `ICM`.`processtime` P SET P.Level= '"+level+"'  WHERE P.processID = '"+procId+ "';");
		try {
			stmt = conn.createStatement();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		try {
			stmt.executeUpdate(query);
			stmt.executeUpdate(query1);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	/**
 * Generate new ID 
 * @param query - the insert query
 * @return new ID of auto-generated ID
 * @throws SQLException
 */
	public int AddAutoInc(String insertQuery) throws SQLException
	{
		PreparedStatement stmt;
		try 
		{
			stmt = conn.prepareStatement(insertQuery);
			stmt.executeUpdate(insertQuery, PreparedStatement.RETURN_GENERATED_KEYS);
			conn.setAutoCommit(false);
			conn.commit();
			conn.setAutoCommit(true);
			ResultSet rs = stmt.getGeneratedKeys();
			rs.next();
			int newMID = rs.getInt(1);
			rs.close();
			stmt.close();
			
			return newMID;
			
		}catch(SQLException e){
			return 0;
		}

	}
	
	/**
	 * 
	 * @param query:String - query for create the statement
	 * @return ResultSet
	 */
	public ResultSet queryDB (String query){
		ResultSet qrs = null;
		try {
			stmt = conn.createStatement();
		} catch (SQLException e1) {
			e1.printStackTrace();
			return null;
		}
		try {
			qrs = stmt.executeQuery(query);
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		}

		return qrs;
	}
	
	/**
	 * function set a selected committee member in the selected role
	 * @param newID:int - the new committee member Id
	 * @param role:int - the role of the committe member that we want to set
	 * @return 0-Change Failed or 1-Successfull change
	 */
	public int SetComMember(int newID,String role){
		ResultSet qrs = null;
		String delQuery;
		String findQuery = new String("SELECT cmID FROM `ICM`.`committeemembers` C WHERE C.cmRole='"+role+"';");
		String addQuery = new String("INSERT INTO `ICM`.`committeemembers` (cmID,cmRole) VALUES('" + newID + "','" + role +"');");
		int key=-1;
		try {
			stmt = conn.createStatement();
			qrs=stmt.executeQuery(findQuery);
		} catch (SQLException e) {
			System.out.println("FailedFindQuery");
			return(0);
		}
		try {
			if(qrs.next())
			key = qrs.getInt(1);
			qrs.close();
			System.out.println("The Key Is: " + key);
		} catch (SQLException e1) {
			e1.printStackTrace();
		}
		if(key!=-1)
		delQuery = new String("DELETE FROM `ICM`.`committeemembers` WHERE cmID='"+key+"';");
		else return(0);
		try {
			stmt.executeUpdate(delQuery);
		} catch (SQLException e) {
			System.out.println("FailedDelQuery");
			return(0);
		}
		try {
			stmt.executeUpdate(addQuery);
		} catch (SQLException e) {
			System.out.println("FailedAddQuery");
			return(0);
		}
	return(1);
	}
	
	/**
	 * function get the estimation report of a selected process
	 * @param proID:int - the process id that we want it's Report
	 * @return ArratList<String> - the details of the estimation report
	 */
	public ArrayList<String> getEsReportFromDB(int proID){
		ArrayList<String> mMap = new ArrayList<String>();
		ResultSet qrs = null;
		String query = new String("SELECT * FROM `ICM`.`EstReports` ESR WHERE ESR.proID='"+ proID +"';");
		try {
			stmt = conn.createStatement();
			qrs = stmt.executeQuery(query);
			if(qrs.next()){
				for(int i=1;i<10;i++)
					mMap.add(qrs.getString(i));
				}
			qrs.close();
		} catch (SQLException e) {
			e.printStackTrace();
			System.out.println("getEsReportFromDB Query Fail");
		}
		return mMap;
	}
	
	/**
	 * function adds an estimation report to database
	 * @param procID:int - the process that has been estimated
	 * @param system:String - the selected system
	 * @param res:String - the reason for estimation
	 * @param limit:String - limitations
	 * @param Hazards:String - hazards/risks by the change
	 * @param time:int - the time for the change to be done
	 * @throws SQLException - When report insertion failed
	 */
	public void addEstimationToDB(int procID,String system, String desc,String res, String limit , String Hazards , int time) throws SQLException {
		String addQuery= new String("INSERT INTO `ICM`.`EstReports` (proID,sysChange,description,result,limitations,hazards,estimatedTime,mem1ReadOk,mem2ReadOk) VALUES (" + procID + ",'" + system + "','" + desc + "','"+ res + "','"+ limit + "','" + Hazards + "','" + time+"','0','0');");
		stmt= conn.createStatement();
		stmt.executeUpdate(addQuery);
		
	}
	
/**
 * Function update the fail report of the tester
 * @param procId:int - The process ID
 * @param reason:String - The report reason
 */
public void updateTestingFailReport(int procId, String reason) {
		
		String query = new String("UPDATE `ICM`.`process` P SET P.testingFail = '" +reason+ "' WHERE P.ProcessID = '"+procId+"';");
		try {
			stmt = conn.createStatement();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		try {
			stmt.executeUpdate(query);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

/**
 * Function update the reads of the report
 * @param memNum
 * @param repID
 */
public void updateReportRead(int memNum, int repID) {
	String Query = new String("UPDATE `ICM`.`EstReports` ESR SET ESR.Mem"+memNum+"ReadOk ='1' WHERE ESR.proID ='"+repID+"';");
	try{
		stmt = conn.createStatement();
		stmt.executeUpdate(Query);
		}
	catch (SQLException e) {
		}
	}

/**
 * 
 * @return ArrayList<String>
 */
public ArrayList<String> getAllEsReportFromDB() {
	ArrayList<String> mMap = new ArrayList<String>();
	ResultSet qrs = null;
	String query = new String("SELECT * FROM `ICM`.`EstReports` ESR ;");
	try {
		stmt = conn.createStatement();
		qrs = stmt.executeQuery(query);
		while(qrs.next()){
				mMap.add(qrs.getString(1));
			}
		qrs.close();
	} catch (SQLException e) {
		e.printStackTrace();
		System.out.println("getEsReportFromDB Query Fail");
	}
	return mMap;
}

/**
 * function Check if the report have been read
 * @param reportId:int - the report ID
 * @return 1 if both have read the report , 0 otherwise
 */
public int CheckReadEstRep(int reportId) {
	ResultSet qrs = null;
	int mem1ok=0,mem2ok=0;
	ArrayList<String> mMap = new ArrayList<String>();
	String Query = new String("SELECT mem1ReadOk,mem2ReadOk FROM `ICM`.`estreports` ESR WHERE ESR.proID ='" + reportId + "';");
	try {
		stmt = conn.createStatement();
		qrs = stmt.executeQuery(Query);
		if(qrs.next())
		{
			mem1ok=Integer.parseInt(qrs.getString(1));
			mem2ok=Integer.parseInt(qrs.getString(2));
			System.out.println("mem1:"+qrs.getString(1)+","+"mem2:"+qrs.getString(2));
		}
		qrs.close();
		if((mem1ok==1)&&(mem2ok==1))
			return(1);
		else
			return(0);
	} catch (SQLException e) {
		e.printStackTrace();
		return(0);
		}
	}

/**
 * 
 * @return ArrayList<String>
 */
 public ArrayList<String> GetAllWorkersList(){
	ArrayList<String> mMap = new ArrayList<String>();
	ResultSet qrs = null;
	String query = new String("SELECT userID,userName,userLastname FROM `ICM`.`login` L WHERE L.permisionLevel = '4' ;");
	try {
		stmt = conn.createStatement();
		qrs = stmt.executeQuery(query);
		while(qrs.next()){
			for(int i=1;i<=3;i++) {
				mMap.add(qrs.getString(i));
				mMap.add(" "); }
			mMap.add("\n");
			}
		  
		qrs.close();
	} catch (SQLException e) {
		e.printStackTrace();
		System.out.println("Getting Workeres Query Fails");
	}
	return mMap;
} 

 /**
  * Function delete estimator report
  * @param reportID:int - The report ID
  * @return 1 for success , 0 for fail
  */
public int delEsReport(int reportID) {
	String Query = new String("DELETE FROM `ICM`.`estreports` WHERE proID='"+reportID+"';");
	try {
		stmt = conn.createStatement();
		stmt.executeUpdate(Query);
	} catch (SQLException e) {
		e.printStackTrace();
		return(0);
	}
	return(1);
	}

	/**
	 * 	function update the position of the execution leader 
	 * @param workerID:int - The worker ID
	 * @param procID:int - The process ID
	 */
	public void updateExecldr(int workerID, int procID){
		String query = new String("UPDATE `ICM`.`process` P SET P.ExecLeaderID = '"+workerID+"' WHERE P.processID = "+ procID + ";");
		
		try {
			stmt = conn.createStatement();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		try {
			stmt.executeUpdate(query);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}





/**
 * function returns all the processes that are active
 * @return Result Set Of ProcessIds
 */
public ResultSet GetAllActiveProcess() {
	String getAllActiveProcQuery = new String("SELECT ProcessID FROM `ICM`.`process` P WHERE P.Status = 'Active';");
	try {
		stmt = conn.createStatement();
		return(stmt.executeQuery(getAllActiveProcQuery));
		} catch (SQLException e) {
			e.printStackTrace();
			return(null);
		}
	}

/**
 * Function change the time 
 * @param procID:int - The process ID
 */
public void DayEndedSoChangeTimeOfProcess(int procID) {
	String QuerySub1 = new String ("UPDATE `ICM`.`processtime` SET timeEntered=timeEntered-1 WHERE processID ='"+procID+"';");
	String QueryAdd1 = new String ("UPDATE `ICM`.`processtime` SET timeSinceBegin=timeSinceBegin+1 WHERE processID ='"+procID+"';");
	
	try {
		stmt = conn.createStatement();
		stmt.executeUpdate(QuerySub1);
		stmt = conn.createStatement();
		stmt.executeUpdate(QueryAdd1);		
	} catch (SQLException e) {
		e.printStackTrace();
	}
	
	}

/**
 * 
 * @return 
 */
public int getIsDepManager() {
	ResultSet qrs=null;
	String getDepManQuery = new String("SELECT userID FROM `ICM`.`login` L WHERE L.permisionLevel = '5';");
	int ans=0;
	try {
		stmt = conn.createStatement();
		qrs=stmt.executeQuery(getDepManQuery);
		if(qrs.next())
		ans=qrs.getInt(1);
		qrs.close();
		return(ans);
	} catch (SQLException e) {
		e.printStackTrace();
	}
	return 0;
}	
	
/**
 * 
 * @param proID:int - The process ID
 * @return ArrayList<String>
 */
public ArrayList<String> getRequestsFromDB(int proID){
	ArrayList<String> mMap = new ArrayList<String>();
	ResultSet qrs = null;
	String query = new String("SELECT * FROM `ICM`.`requests` REQ WHERE REQ.ReqID='"+ proID +"';");
	try {
		stmt = conn.createStatement();
		qrs = stmt.executeQuery(query);
		if(qrs.next()){
			for(int i=3;i<9;i++)
				mMap.add(qrs.getString(i));
			}
		qrs.close();
	} catch (SQLException e) {
		e.printStackTrace();
		System.out.println("get Requests Fail");
	}
	return mMap;
}

/**
 * 
 * @param proID:int - The process ID
 * @return Number of days left for a phase
 */
public int getTimeLeftForProcess(int proID) {
	int ans=0;
	String Query = new String("SELECT timeEntered FROM `ICM`.`processtime` WHERE processID = '"+proID+"';");
	ResultSet qrs = null;
	try {
		stmt = conn.createStatement();
		qrs = stmt.executeQuery(Query);
		if(qrs.next())
		ans=qrs.getInt(1);
		qrs.close();
		return(ans);
	} catch (SQLException e) {
		e.printStackTrace();
		return(-1000);
		}
	
	}
/**
 * Function sends system messages
 * @param nMsg
 */
public void SendMessageInSystem(NotificationMessage nMsg) {
	int boolToint;
	if(!nMsg.getread()) boolToint=0;
		else boolToint=1;		
	String addQuery = new String("INSERT INTO `ICM`.`messages`(usrID,processID,type,msg,date,readOk) VALUES (" + nMsg.getusrID() + ",'" + nMsg.getprocessID() + "','" + nMsg.gettype() + "','" + nMsg.getmsg() + "',CURDATE(),'" + boolToint +"');");
	try {
		stmt = conn.createStatement();
		stmt.executeUpdate(addQuery);
	} catch (SQLException e) {
		e.printStackTrace();
	}
}

public ArrayList<String> getAllrequestID() throws SQLException{
	ArrayList<String> mMap = new ArrayList<String>();
	ResultSet qrs = null;
	String query = new String("SELECT ReqID FROM `ICM`.`requests` ;");
	try {
	stmt = conn.createStatement();
	qrs = stmt.executeQuery(query);
	while (qrs.next()) {
		mMap.add(qrs.getString(1));
	}
	qrs.close();
	} catch (SQLException e) {
		e.printStackTrace();
		System.out.println("get RequestsID Fail");
	}
	return mMap;
}

/**
 * function update execution time flag
 * @param procId - Process's ID
 * @param responOk - 1 OK , 0 otherwise
 */
public void updateExecTimeFlag(int procId, int responOk) {
	String query = new String("UPDATE `ICM`.`processtime` P SET P.ExectFlag = '"+responOk+"' WHERE P.processID = "+ procId + ";");
	
	try {
		stmt = conn.createStatement();
	} catch (SQLException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	}
	try {
		stmt.executeUpdate(query);
	} catch (SQLException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	}
	
}


public String getReqFileName(int reqID) {
	ResultSet qrs=null;
	String getFileName = new String("SELECT FileName FROM `ICM`.`requests` R WHERE R.ReqID ='"+ reqID+ "';");
	String ans=null;
	try {
		stmt = conn.createStatement();
		qrs=stmt.executeQuery(getFileName);
		if(qrs.next())
		ans=new String(qrs.getString(1));
		qrs.close();
		return(ans);
	} catch (SQLException e) {
		e.printStackTrace();
		return null;
		}
	}

/**
 * Function create new time extension request
 * @param procID - process's ID
 * @param phase - phase number
 * @param numOfDays - number of days asked
 * @param msg - message
 */
public void createNewTmExecRqst(int procID, int phase, int numOfDays, String msg) {
	String addQuery= new String("INSERT INTO `ICM`.`timeExtencionRequest` (procID,phase,msg,numOfDays,flag) VALUES (" + procID + "," + phase + ",'" + msg + "',"+ numOfDays + ",'0');");
	try {
		stmt= conn.createStatement();
	} catch (SQLException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	}
	try {
		stmt.executeUpdate(addQuery);
	} catch (SQLException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	}
	
}

public void getAndUpdtExtRqst(GetAllPack pack){
	String updatequery = new String("UPDATE `ICM`.`timeextencionrequest` P SET P.flag = '1' WHERE P.processID = "+ pack.getInitID() + " AND P.phase = '"+pack.getPhase()+"';");
	String Query = new String("SELECT * FROM `ICM`.`timeextencionrequest` P WHERE P.procID = '"+pack.getInitID()+"'AND P.phase = '"+pack.getPhase()+"';");
	ResultSet qrs = null;
	
	try {
		stmt= conn.createStatement();
	} catch (SQLException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	}
	try {
		qrs = stmt.executeQuery(Query);
	} catch (SQLException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	}
	try {
		if(qrs.next()){
			pack.setFlag(qrs.getInt(6));
			pack.setDays(qrs.getInt(5));
			pack.setMsg(qrs.getString(4));
		}
		qrs.close();
	} catch (SQLException e1) {
		// TODO Auto-generated catch block
		e1.printStackTrace();
	}
	
	try {
		stmt= conn.createStatement();
	} catch (SQLException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	}
	try {
		
		stmt.executeUpdate(updatequery);
	} catch (SQLException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	}	
}

/**
 * Function add week to the date
 * @param startDate - start date to add the week
 * @return
 */
public String addWeekToDate(String startDate){
	int daysinmonth;
	int tempCurDayInDate=0;
	int tempMonth=0;
	int tempYear=0;
	String ans=new String();
	tempYear=Integer.parseInt(startDate.substring(0, 4));
	tempCurDayInDate=Integer.parseInt(startDate.substring(startDate.lastIndexOf('-')+1).trim());
	tempMonth=Integer.parseInt(startDate.substring(5, 7));
	Calendar returnDate = Calendar.getInstance();
	switch(tempMonth)
		{
		case 1:daysinmonth=31;
				break;
		case 2:daysinmonth=28;
				break;
		case 3:daysinmonth=31;
				break;
		case 4:daysinmonth=30;
				break;
		case 5:daysinmonth=31;
				break;
		case 6:daysinmonth=30;
				break;
		case 7:daysinmonth=31;
				break;
		case 8:daysinmonth=31;
				break;
		case 9:daysinmonth=30;
				break;
		case 10:daysinmonth=31;
				break;
		case 11:daysinmonth=30;
				break;
		case 12:daysinmonth=31;
				break;
				default:daysinmonth=31;
		}
	if(tempMonth!=12)
	{
		if((tempCurDayInDate+7)<=daysinmonth)
			tempCurDayInDate=tempCurDayInDate+7;
			else {tempCurDayInDate=(tempCurDayInDate+7)-daysinmonth;
					tempMonth=tempMonth+1;}
		if((tempMonth>9)&&(tempCurDayInDate>9))
			ans = new String(Integer.toString(tempYear)+"-"+Integer.toString(tempMonth)+"-"+Integer.toString(tempCurDayInDate));
		if((tempMonth>9)&&(tempCurDayInDate<10))
			ans = new String(Integer.toString(tempYear)+"-"+Integer.toString(tempMonth)+"-0"+Integer.toString(tempCurDayInDate));
		if((tempMonth<10)&&(tempCurDayInDate>9))
			ans = new String(Integer.toString(tempYear)+"-0"+Integer.toString(tempMonth)+"-"+Integer.toString(tempCurDayInDate));
		if((tempMonth<10)&&(tempCurDayInDate<10))
			ans = new String(Integer.toString(tempYear)+"-0"+Integer.toString(tempMonth)+"-0"+Integer.toString(tempCurDayInDate));	
	}
	else
	{
		if((tempCurDayInDate+7)<=daysinmonth)
			tempCurDayInDate=tempCurDayInDate+7;
			else tempCurDayInDate=(tempCurDayInDate+7)-daysinmonth;
		if(tempCurDayInDate<10)
			ans = new String(Integer.toString(tempYear+1)+"-0"+Integer.toString(1)+"-0"+Integer.toString(tempCurDayInDate));
			else
			ans = new String(Integer.toString(tempYear+1)+"-0"+Integer.toString(1)+"-"+Integer.toString(tempCurDayInDate));
	}
	return(ans);
  }

public int CheckAfter(String startDate,String endDate)
{
int StarttempYear=Integer.parseInt(startDate.substring(0, 4));
int StarttempCurDayInDate=Integer.parseInt(startDate.substring(startDate.lastIndexOf('-')+1).trim());
int StarttempMonth=Integer.parseInt(startDate.substring(5, 7));
int EndtempYear=Integer.parseInt(endDate.substring(0, 4));
int EndtempCurDayInDate=Integer.parseInt(endDate.substring(startDate.lastIndexOf('-')+1).trim());
int EndtempMonth=Integer.parseInt(endDate.substring(5, 7));	
if((StarttempYear<EndtempYear)||((StarttempYear==EndtempYear)&&(StarttempMonth<EndtempMonth))||((StarttempYear==EndtempYear)&&(StarttempMonth==EndtempMonth)&&(StarttempCurDayInDate<EndtempCurDayInDate))) return(1);
return(0);
}

public ArrayList<String> StatNumOfEnded(String startDate, String endDate) {
	String tempStart = startDate;
	String tempEnd = addWeekToDate(startDate);
	ArrayList<String> NumOfEnded = new ArrayList<String>();
	int i=0;
	System.out.println("1-Start:"+tempStart+", Temp:"+tempEnd+", End:"+endDate);
	//Check End In Range
	if(CheckAfter(tempEnd,endDate)==0)
		tempEnd=endDate;
	System.out.println("2-Start:"+tempStart+", Temp:"+tempEnd+", End:"+endDate);

	while(!tempEnd.equals(endDate))
		{
		System.out.println(i+"---Start:"+tempStart+", Temp:"+tempEnd+", End:"+endDate);
		ResultSet qrs = null;
		String Query = new String("SELECT COUNT(*) FROM `ICM`.`processdates` PD WHERE ((PD.endDate<'"+tempEnd+"') AND (PD.endDate>'"+tempStart+"'));");
		///Treat String
		try {
			stmt= conn.createStatement();
			qrs = stmt.executeQuery(Query);
			if(qrs.next())
				NumOfEnded.add(qrs.getString(1));
			qrs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		//Manage The Next Date To Check
		tempStart=tempEnd;
		tempEnd=addWeekToDate(tempEnd);
		if(CheckAfter(tempEnd,endDate)==0)
			tempEnd=endDate;
		System.out.println(i+":-"+NumOfEnded.get(i));
		i++;
		}
	//for(i=0;i<NumOfActive.size();i++)
		//System.out.println(i+":-"+NumOfActive.get(i));
	return NumOfEnded;
	}

public ArrayList<String> StatNumOfFrozen(String startDate, String endDate) {
	String tempStart = startDate;
	String tempEnd = addWeekToDate(startDate);
	ArrayList<String> NumOfFrozen = new ArrayList<String>();
	int i=0;
	System.out.println("1-Start:"+tempStart+", Temp:"+tempEnd+", End:"+endDate);
	//Check End In Range
	if(CheckAfter(tempEnd,endDate)==0)
		tempEnd=endDate;
	System.out.println("2-Start:"+tempStart+", Temp:"+tempEnd+", End:"+endDate);

	while(!tempEnd.equals(endDate))
		{
		System.out.println(i+"---Start:"+tempStart+", Temp:"+tempEnd+", End:"+endDate);
		ResultSet qrs = null;
		String Query = new String("SELECT COUNT(*) FROM `ICM`.`processdates` PD WHERE (((PD.UnSuspendDate<'"+tempEnd+"') AND (PD.UnSuspendDate>'"+tempStart+"')) OR ((PD.suspendDate<'"+tempEnd+"') AND (PD.suspendDate>'"+tempStart+"')) OR ((PD.UnSuspendDate IS NULL) AND (PD.suspendDate<'"+tempEnd+"')));");
		///Treat String
		try {
			stmt= conn.createStatement();
			qrs = stmt.executeQuery(Query);
			if(qrs.next())
				NumOfFrozen.add(qrs.getString(1));
			qrs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		//Manage The Next Date To Check
		tempStart=tempEnd;
		tempEnd=addWeekToDate(tempEnd);
		if(CheckAfter(tempEnd,endDate)==0)
			tempEnd=endDate;
		System.out.println(i+":-"+NumOfFrozen.get(i));
		i++;
		}
	//for(i=0;i<NumOfActive.size();i++)
		//System.out.println(i+":-"+NumOfActive.get(i));
	return NumOfFrozen;
	}


public ArrayList<String> StatNumOfActive(String startDate, String endDate) {
	String tempStart = startDate;
	String tempEnd = addWeekToDate(startDate);
	ArrayList<String> NumOfActive = new ArrayList<String>();
	int i=0;
	System.out.println("1-Start:"+tempStart+", Temp:"+tempEnd+", End:"+endDate);
	//Check End In Range
	if(CheckAfter(tempEnd,endDate)==0)
		tempEnd=endDate;
	System.out.println("2-Start:"+tempStart+", Temp:"+tempEnd+", End:"+endDate);

	while(!tempEnd.equals(endDate))
		{
		System.out.println(i+"---Start:"+tempStart+", Temp:"+tempEnd+", End:"+endDate);
		ResultSet qrs = null;
		String Query = new String("SELECT COUNT(*) FROM `ICM`.`processdates` PD WHERE ((PD.startDate<'"+tempEnd+"') AND ((PD.endDate IS NULL) OR (PD.endDate>'"+tempStart+"')) AND ((PD.suspendDate IS NULL) OR (PD.suspendDate>'"+tempStart+"') OR (PD.UnSuspendDate<'"+tempEnd+"')));");
		///Treat String
		try {
			stmt= conn.createStatement();
			qrs = stmt.executeQuery(Query);
			if(qrs.next())
				NumOfActive.add(qrs.getString(1));
			qrs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		//Manage The Next Date To Check
		tempStart=tempEnd;
		tempEnd=addWeekToDate(tempEnd);
		if(CheckAfter(tempEnd,endDate)==0)
			tempEnd=endDate;
		System.out.println(i+":-"+NumOfActive.get(i));
		i++;
		}
	//for(i=0;i<NumOfActive.size();i++)
		//System.out.println(i+":-"+NumOfActive.get(i));
	return NumOfActive;
	}
/**
 * function casting calendar to string
 * @param date - The date
 * @return
 */
private String calenderToString(Calendar date) {
	String temp = date.get(1) + "-"; //String With The Current Year And "-"
	if (date.get(2) < 10) temp += "0" + (date.get(2) + 1) + "-"; //Add To String The Current Month And "-", if The Month is a marked by one figure add 0 before
	else temp += (date.get(2) + 1) + "-";//Add To String The Current Month And "-"
	if (date.get(5) < 10) temp += "0" + date.get(5);//Add To String The Current Day, if The Month is a marked by one figure add 0 before
	else temp += date.get(5);//Add To String The Current Month
	return temp;
}

public  ArrayList<String> getAllsupProcess(int supID) throws SQLException{
		String Query = new String("SELECT * FROM `ICM`.`process` P WHERE P.supervisorID = "+supID+";");
		ResultSet qrs = null;
		ArrayList<String> NumOfActive= new ArrayList<String>();
		try {
			stmt= conn.createStatement();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		try {
			qrs = stmt.executeQuery(Query);
		} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
		}
		while (qrs.next()){
			NumOfActive.add(qrs.getString(1));
			}
		qrs.close();
		return NumOfActive;
	}
/**
 * Function calculate and sum the number of approved extensions
 * @return
 * @throws SQLException
 */
public int getSumOfApprovedExtensions() throws SQLException{
	int sum=0;
	ResultSet qrs=null;
	String Query= new String("SELECT SUM(numOfdays) FROM `ICM`. `timeextencionrequest` ;"); 
	try {
	stmt = conn.createStatement();
	} catch (SQLException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	}
	try {
	qrs = stmt.executeQuery(Query);
	} catch (SQLException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	}
		if(qrs.next())
			sum= qrs.getInt(1);
		qrs.close();

	return sum;
}
public int getcountOfpreformance() throws SQLException{
	int count=0;
	ResultSet qrs=null;
	String Query= new String("SELECT COUNT(timeExecReqID) FROM `ICM`. `timeextencionrequest` ;"); 
	try {
	stmt = conn.createStatement();
	} catch (SQLException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	}
	try {
	qrs = stmt.executeQuery(Query);
	} catch (SQLException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	}
		if(qrs.next())
			count= qrs.getInt(1);
		qrs.close();
	return count;
}

}
